import datetime
import difflib
import getpass
import os
import re
import time
import tkinter as tk
import openpyxl as vb
import pymssql
import pymysql
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side



def read_write(lab_engineers,ip_address):

    #1.获取当前日期  
    todaycolumn=datetime.datetime.now().strftime('%Y-%m-%d') 
    today=datetime.datetime.now().strftime('%m%d')
    tomorrowcolumn=(datetime.datetime.now()+datetime.timedelta(days=1)).strftime('%Y-%m-%d')     
    pre_days_upload=(datetime.datetime.now()+datetime.timedelta(days=0)).strftime('%Y-%m-%d')
    # 格式化pre_days_upload, 为了比较大小
    Xdaysago = datetime.datetime.strptime(pre_days_upload,"%Y-%m-%d") 

    pre_days_upload1=(datetime.datetime.now()+datetime.timedelta(days=-60)).strftime('%Y-%m-%d')  
    mdaysago = datetime.datetime.strptime(pre_days_upload1,"%Y-%m-%d") 
    pre_days_upload2=(datetime.datetime.now()+datetime.timedelta(days=-1)).strftime('%Y-%m-%d')  
    twodaysago = datetime.datetime.strptime(pre_days_upload2,"%Y-%m-%d") 



    #3.建立mysql的连接
    conn = pymysql.connect(host=ip_address,user='labuser',db='exceltodb',
            password='123456',port=3306,charset='utf8')
    # 获得游标
    cursor = conn.cursor()

    #从DB中取出test_name_simple放入一个列表里,用来匹配test_name的简写形式。
    test_name_simple=[]
    cursor.execute('''select test_name from test_name_list''')
    testname_info=cursor.fetchall()
    for i in testname_info:
        if i[0] not in test_name_simple and i[0]!='':
            test_name_simple.append(i[0])        #放进来的都是不重复的，非空的testname名字

    foldable_product_list=[]
    cursor.execute('''select product from coefficient_infor where product!='Candybar' ''')
    product_returns = cursor.fetchall()
    for i in product_returns:
        if i[0] not in foldable_product_list:
            foldable_product_list.append(i[0])
    # print(foldable_product_list)

    #------------below rows only for debug, delete imported rows---------------    
    # # 暂停点
    # print("按'回车键'或'空格键'继续......")        
    # while True:             
    #     char=ord(msvcrt.getch()) 
    #     if char==13 or char==32:         
    #         break
    #------------------------------------------------------------------


    server_dir=r'\\10.114.184.9\Tcc_Rel\04 Task_Assignment\产品实验跟踪'
    # server_dir=r'\\10.114.184.9\Tcc_Rel\04 Task_Assignment\产品实验跟踪backup'


    pattern_fill = PatternFill(fill_type="solid",fgColor="FFFF00")
    alignment = Alignment(horizontal='center',vertical='center')
    border = Border(left=Side(border_style='thin', color='000000'),
                right=Side(border_style='thin', color='000000'),
                top=Side(border_style='thin', color='000000'),
                bottom=Side(border_style='thin', color='000000'))
    fontsetting = Font(name=u'Arial', bold=False, italic=False, size=10)



    test_plan_file=r'C:\自动分活儿\Reliability Test plan.xlsx'        
    test_plan_backup = r'C:\自动分活儿\Reliability Test plan 导入前备份.xlsx'     
    workbook1 = vb.load_workbook(test_plan_file)  
    workbook1.save(test_plan_backup)  
    workbook1 = vb.load_workbook(test_plan_file)  #20250405新增
    sheet_names=['ALT','DVT','Accessory']

    #获取已导入到test plan表格中的实验号test_no_list列表，为的是在导入时与之对比，只有不在这个list中的实验才会被导入
    test_no_list=[]
    

    for i in sheet_names:
        plan_worksheet=workbook1[i]
        n=plan_worksheet.max_row-5  #防止有时sheet行数太少的情况
        # print(n)        
        if n>=250:
            n=250
        for rownumber in range(plan_worksheet.max_row,plan_worksheet.max_row-n,-1):      #range,从最大行开始往回遍历
            for cell in plan_worksheet[rownumber][2:3]:     #以[]最后一位为准       
                if cell.value is not None:
                    test_no ='WH'+re.findall('\d{6}',str(cell.value))[0] 
                    test_no_list.append(test_no)

    # print(test_no_list)   
    print('获取test_no_list:',len(test_no_list))   
        

    #文件处理：列出指定服务器目录中的所有待导入文件，获取每个文件的名称，取得工程师名字，及产品名字
    # result = os.listdir(server_dir)
    result = [f for f in os.listdir(server_dir) if not f.startswith('~$')] # 不显示临时文件
    for file in result:
        filename = os.path.splitext(file)[0]  
        engineer_name_list=re.findall(r'([\u4e00-\u9fa5]+)',filename)  #匹配中文，返回是列表
        # 验证文件名是否包含Test plan of 并包含中文字符：
        if 'Test plan of' not in filename or engineer_name_list==[]:
            os.remove(os.path.join(server_dir,file))
            print('删除不合规文件：',file)
        
        #20230318新加入判断文件修改时间，把时间超过2个月的文件删除掉
        filetime = os.path.getmtime(os.path.join(server_dir,file))   
        timearr = time.localtime(filetime)     #输出：time.struct_time(tm_year=2020, tm_mon=11, tm_mday=15, tm_hour=12, tm_min=49, tm_sec=10, tm_wday=6, tm_yday=320, tm_isdst=0)
        mytime = time.strftime('%Y-%m-%d %H:%M:%S',timearr)
        # print(mytime)    #2020-11-15 12:49:10
        # print(mdaysago)
        if mytime<str(mdaysago):    #删掉60天前文件
            os.remove(os.path.join(server_dir,file))
            print('删除太久之前的文件：',file)

    #------------below rows only for debug, delete imported rows---------------    
    # # 暂停点
    # print("按'回车键'或'空格键'继续......")        
    # while True:             
    #     char=ord(msvcrt.getch()) 
    #     if char==13 or char==32:         
    #         break
    #------------------------------------------------------------------

    result = os.listdir(server_dir)
    dict={}
    dict_for_record={}
    for file in result:
        #20230323新加入判断文件修改时间，只导入最近两天修改过的文件
        filetime = os.path.getmtime(os.path.join(server_dir,file))   
        timearr = time.localtime(filetime)     #输出：time.struct_time(tm_year=2020, tm_mon=11, tm_mday=15, tm_hour=12, tm_min=49, tm_sec=10, tm_wday=6, tm_yday=320, tm_isdst=0)
        mytime = time.strftime('%Y-%m-%d %H:%M:%S',timearr)
        # print(mytime,twodaysago)    #2020-11-15 12:49:10
        # print(mdaysago)

        #只导入最近2天有更新的文件
        if mytime>=str(twodaysago):

            print('待读取文件：',file,mytime)
            filename = os.path.splitext(file)[0]  
            engineer_name_list=re.findall(r'([\u4e00-\u9fa5]+)',filename)  #匹配中文，返回是列表
            lab_engineer=engineer_name_list[0]
            product_name = re.findall(r'of (.*?) ', filename)[0] #匹配of 和空格之间的产品名
            print('待导入产品：',product_name)
            #设置一个导入某种产品数量的计算器
            m=0

            if lab_engineer in lab_engineers:
                filewithdir=os.path.join(server_dir,file)
                workbook = vb.load_workbook(filewithdir)
                worksheet = workbook.active  #当前工作表，保存时为当前的那个sheet
                print('打开待导入sheet',worksheet)    

                y=worksheet.max_row                      
                
                if y>=105:
                    start_line=y-100
                    print(file,'表格总行数：',y,'  *****注意：当总行数>100, 只读最后100行')
                else:
                    start_line=2
                    print(file,'表格总行数：',y)
                
                
                
                #20250405新增
                workbook1 = vb.load_workbook(test_plan_file)  #20250405新增
                
                worksheet_alt=workbook1['ALT']
                worksheet_dvt=workbook1['DVT']
                worksheet_accessory=workbook1['Accessory']
                
                #逐行读取每个人每个产品的plan表格。只读取4列：实验号，reuse, arrdate,comments 
                for r in range(start_line,y+1):  #之前 是从2开始，20250408改成start_line    
                    test_no_original = str(worksheet.cell(r,6).value)
                    test_no_digit = re.findall('\d{6}',test_no_original)
                    if test_no_digit!=[]:
                        test_no='WH'+test_no_digit[0]   # 之前：test_no = worksheet.cell(r,6).value

                    qty0 = worksheet.cell(r,5).value   #不采用，用ALTDB查到的，实际上只读取了4列：实验号，reuse, arrdate,comments
                    re_use= worksheet.cell(r,7).value
                    arr_date= worksheet.cell(r,8).value
                    comments= worksheet.cell(r,9).value
                
                    if re_use==1:
                        priority=3
                    else:
                        priority=4     
            
                    #当前导入条件：当日到样且未导入过的，之前导入的为几日内，所以命名为Xdaysago
                    if arr_date is not None and arr_date==Xdaysago and test_no not in test_no_list:
                        m=m+1  #记录写入的实验号数量
                        #ALT DB 查询：通过test_no查出 Qty, vintage, test_name, test_name 要变为简写，通过test_name要给出category
                        altserver='100.64.6.151'
                        conn=pymssql.connect(host=altserver,user = "global_alt_guest",password = "global@alt@guest",database = "ALTDBV3")
                        cursor = conn.cursor()            
                        cursor.execute('''SELECT R.Test_Seqno AS Test_No,R.Quantity AS Qty, P.InternalName AS Product, CASE WHEN PV.Vintage_Name IS NULL THEN '' ELSE 
                        PV.Vintage_Name END + CASE WHEN R.Vintage IS NULL THEN '' ELSE R.Vintage END as Vintage, T.Name AS Test_Name
                        FROM Registration AS R
                        LEFT OUTER JOIN Products AS P ON R.Product_ID = P.ID
                        LEFT OUTER JOIN Product_Vintage AS PV ON PV.ID = R.Product_Vintage_ID
                        LEFT OUTER JOIN Tests AS T ON T.ID = R.Test_ID
                        WHERE R.Test_Seqno=%s 
                        ORDER BY R.Test_Seqno
                        ''',test_no)
                        infor=cursor.fetchone() 
                        # print(infor[0],infor[1],infor[2],infor[3],infor[4])    
                        if infor is None:
                            tk.messagebox.showwarning(title='Hi',message=product_name+'实验号:'+test_no+'有错误！')      
                        qty=infor[1]
                        vintage=infor[3]
                        test_name=infor[2]
                        match = re.search(r'^(.*?)[\s\d]', test_name)  #此句和下面一句共同实现：取出一个字符串中空格或者数字之前的字符串                     
                        if match is not None:
                            name_cut = match.group(1)
                        else:
                            name_cut = test_name
                        #根据从ALT DB取出的产品名，去查询相应的ODM/FT/In-house,IPx_level信息
                        conn = pymysql.connect(host=ip_address,user='labuser',db='exceltodb',
                                password='123456',port=3306,charset='utf8')
                        # 获得游标
                        cursor = conn.cursor()
                        cursor.execute('''select product,category,IP_level from alt_infor where product like %s  ''',(name_cut+'%') )
                        rows=cursor.fetchall()
                        
                        In_houseFTODM=rows[0][1]  #ODM or In_house, FT   
                        if rows[0][2] !='':                       
                            ip_infor=rows[0][2]   #防水等级   
                        else:
                            ip_infor='0'
                        


                
                        #基于Full test name去写出category
                        fullname=infor[4]
                        if 'DVT' in fullname and 'Eval' not in fullname and 'C -' not in fullname and '.A' not in fullname and '.B' not in fullname and '.C' not in fullname:
                            category='DVT'
                        elif 'DVT' in fullname and 'Eval' in fullname:
                            category='DVT Eval'
                        elif 'MD ALT 2019' in fullname and 'Eval' in fullname:
                            category='ALT Eval'
                        elif 'MD ALT 2019' in fullname and 'Eval' not in fullname:
                            category='Full ALT'
                        elif 'MD C-ALT 2019' in fullname:
                            category='CALT'
                        elif 'DVT' in fullname and 'C -' in fullname and '.C' not in fullname:
                            category='CDVT'
                        elif 'ATT' in fullname:
                            category='ATT'
                        elif 'TMO' in fullname:
                            category='TMO'
                        elif 'Charger' in fullname:
                            category='Charger'
                        elif 'Cable' in fullname:
                            category='Cable'
                        elif 'Headset' in fullname:
                            category='Headset'
                        elif 'LCM/TP' in fullname:
                            category='LCM/TP'
                        elif 'PAD' in fullname:
                            category='PAD'
                        elif 'Temperature & Humidity' in fullname:
                            category='Evaluation/DOE'
                        elif 'Film' in fullname:
                            category='Film'
                        elif 'Housing' in fullname:
                            category='Housing'
                        elif 'ERT' in fullname:
                            category='ERT'
                        elif 'Temperature only' in fullname:
                            category='Evaluation/DOE'
                        elif 'Speaker Aging' in fullname:
                            category='DVT Eval'
                        elif 'TP/LCM Rel' in fullname:
                            category='LCM/TP'
                        elif 'Rear Cover/Inlay' in fullname:
                            category='Rear Cover/Inlay'
                        elif 'Pad Rel Test' in fullname:
                            category='PAD'
                        elif 'Pad_Key part' in fullname:
                            category='Pad_Key part'
                        elif 'FPS Rel' in fullname:
                            category='FPS'
                        elif 'Protective Case' in fullname:
                            category='Protective Case'
                        elif 'Label Rel' in fullname:
                            category='Label'
                        elif 'CEC' in fullname:
                            category='CEC'
                        elif 'Softbank' in fullname:
                            category='Softbank'
                        elif 'Docomo' in fullname and 'Docomo' not in fullname:
                            category='Docomo'
                        elif 'FCNT' in fullname:
                            category='FCNT'
                        elif 'Orange' in fullname:
                            category='ORANGE'
                        elif 'ALT XXXX' in fullname:
                            category='ALT Eval'
                        elif 'LotX' in fullname and 'TA' not in fullname:
                            category='LotX'   
                        elif 'UV glue corrode side key test' in fullname:
                            category='Study'  
                        elif 'Torque Study' in fullname:
                            category='Study'  
                        elif 'KDDI' in fullname:
                            category='KDDI'   

                        elif 'TA-' in fullname:
                            category='Accreditation' 

                        elif 'Battery Strength Test' in fullname:
                            category='Study'   
                        elif '.A' in fullname or '.B' in fullname or '.C' in fullname:
                            category='Extreme'                          
                        elif re.match('M[0-9]+',fullname) is not None and re.match('M[0-9]+',fullname).group() in fullname:
                            category='MIL'   

                        else:
                            category='Others'



                        #基于Full test name去查找简化的simple_name, 如果是EDu03这样的，还得多写几行
                        full_name=infor[4]
                        name_cut=full_name[:15]
                        if 'Softbank' in full_name:
                            name_cut=full_name[9:19]
                        if 'TA-MIL-STD-810H' in full_name:
                            name_cut=full_name[:18]
                        simple_name=difflib.get_close_matches(name_cut,test_name_simple,n=1,cutoff=0.1)[0]  #difflib.get_close_matches返回列表，要加[0]得到数值
                        # print(simple_name,'---------',full_name,'---------',category)   
                        
                        #处理特殊的，difflib识别错误的实验
                        if 'MDu11' in full_name:
                            simple_name='MDu11'
                        if 'TMO 6523' in full_name:
                            simple_name='TMO6523 Pencil'                        
                        if 'TMO 483007' in full_name:
                            simple_name='TMO483007 Tool'
                        if 'TMO 6545' in full_name:
                            simple_name='TMO6545'
                        if 'ATT 26' in full_name:
                            simple_name='ATT26 Dust'
                        if 'TMO 6515' in full_name:
                            simple_name='TMO6515 Humidity'
                        if 'ATT 27' in full_name:
                            simple_name='ATT27 UV'
                        if 'ATT 28' in full_name:
                            simple_name='ATT28'
                        if 'DVT MDu45' in full_name:
                            simple_name='MDu45'
                        if 'DVT MDu28' in full_name:
                            simple_name='MDu28'
                        if 'MDu70' in full_name:
                            simple_name='MDu70'
                        if 'MDu73' in full_name:
                            simple_name='MDu73'
                        if 'MDu58' in full_name:
                            simple_name='MDu58'
                        if 'MDu83' in full_name:
                            simple_name='MDu-83'                            
                        if 'ERT Imagers Test (Component Level)' in full_name:
                            simple_name='ERT Component'
                        if 'Temperature & Humidity' in full_name:
                            simple_name='T&H'
                        if 'TP/LCM Rel' in full_name:
                            simple_name='LCM/TP Rel'
                        if 'FPS Rel' in full_name:
                            simple_name='FPS'
                        if 'Label Rel' in full_name:
                            simple_name='Label Rel'
                        if 'CEC' in full_name:
                            simple_name='CEC'
                        if 'ME-001-003-A' in full_name:
                            simple_name='ME-001-A003'
                        if 'ME-004-002-A' in full_name:
                            simple_name='ME-004-A002'   
                        if 'MDu53' in full_name:
                            simple_name='MDu53' 

                        if 'TA-MIL-STD-810H' in full_name:
                            name_cut=full_name[:18]
                            parts = name_cut.split('-')
                            simple_name = f"{parts[0]}-{parts[1]}-{parts[-1]}"  #用-分开，name_cut='TA-MIL-STD-810H-01', 则simple_name=''TA-MIL-01', 依次类推
                        if 'TA-IEC60529' in full_name:
                            parts = full_name.split('-')
                            simple_name = f"{parts[0]}-{parts[-1]}"
                        if 'KDDI' in full_name:
                            simple_name=full_name[:8]

                        if 'TA-LotX Tumbler' in full_name:
                            simple_name = 'TA-Tumbler'
                        if 'TA-LotX Mohs' in full_name:
                            simple_name = 'TA-Mohs'

                        if 'MDu66.A' in full_name:
                            simple_name='MDuA66'
                        if 'MDu64.B' in full_name:
                            simple_name='MDuB64'   
                        if 'MDu64.A' in full_name:
                            simple_name='MDuA64'
                        if 'MXc20.A' in full_name:
                            simple_name='MXcA20'
                        if 'MXc20.B' in full_name:
                            simple_name='MXcB20'
                        if 'MXc20.C' in full_name:
                            simple_name='MXcC20'
                        if 'MDu84.A' in full_name:
                            simple_name='MDuA84'   
                        if 'MDu53.A' in full_name:
                            simple_name='MDuA53'  

                        if 'MDu88.A' in full_name:
                            simple_name='MDuA88'   
                        if 'MDu89.A' in full_name:
                            simple_name='MDuA89'   
                        if 'MDu92.A' in full_name:
                            simple_name='MDuA92'   
                        if 'MDu93.A' in full_name:
                            simple_name='MDuA93'
                        if 'MDu64.C' in full_name:
                            simple_name='MDuC64'
                        if 'LotX MXc06' in full_name:
                            simple_name='MXc-LotX-06'
                        if 'LotX MDu31' in full_name:
                            if In_houseFTODM=='In-house' and ip_infor>='7' and vintage!='EVT' and vintage!='DVT1':
                                simple_name='MDu-LotXAIR-31'
                            else:
                                simple_name='MDu-LotX-31'

                        if 'LotX MDu20' in full_name:
                            simple_name='MDu-LotX-20'
                        if 'LotX MXc21' in full_name:
                            simple_name='MXc-LotX-21'
                        if 'FCNT A01' in full_name:
                            simple_name='FCNT A01'
                        if 'FCNT A05' in full_name:
                            simple_name='FCNT A05'
                        if 'FCNT A06' in full_name:
                            simple_name='FCNT A06'
                        if 'FCNT A07' in full_name:
                            simple_name='FCNT A07'
                        if 'FCNT A19' in full_name:
                            simple_name='FCNT A19'
                        if 'FCNT A20' in full_name:
                            simple_name='FCNT A20'
                        if 'FCNT A27' in full_name:
                            simple_name='FCNT A27'
                        if 'FCNT A29' in full_name:
                            simple_name='FCNT A29'
                        if 'FCNT A40' in full_name:
                            simple_name='FCNT A40'
                        if 'FCNT A68' in full_name:
                            simple_name='FCNT A68'
                        if 'FCNT A71' in full_name:
                            simple_name='FCNT A71'
                        if 'FCNT A72-1' in full_name:
                            simple_name='FCNT A72-1'
                        if 'FCNT A74' in full_name:
                            simple_name='FCNT A74'
                        if 'FCNT A77' in full_name:
                            simple_name='FCNT A77'
                        if 'FCNT' in full_name and 'MDu18' in full_name:
                            simple_name='MDuFCNT18'                                
                        if 'FCNT' in full_name and 'MDu20' in full_name:
                            simple_name='MDuFCNT20'

                        if 'FCNT' in full_name and 'MDu31' in full_name:
                            if In_houseFTODM=='In-house' and ip_infor>='7' and vintage!='EVT' and vintage!='DVT1':
                                simple_name='MDuFCNTAIR31'
                            elif In_houseFTODM!='In-house' and ip_infor>='7' and vintage!='EVT' and vintage!='DVT1':
                                simple_name='MDuFCNTWater31'
                            else:
                                simple_name='MDuFCNT31'


                        if 'FCNT' in full_name and 'MDu45' in full_name:
                            if In_houseFTODM=='In-house' and ip_infor>='7' and vintage!='EVT' and vintage!='DVT1':
                                simple_name='MDuFCNTAIR45'
                            elif In_houseFTODM!='In-house' and ip_infor>='7' and vintage!='EVT' and vintage!='DVT1':
                                simple_name='MDuFCNTWater45'
                            else:
                                simple_name='MDuFCNT45'

                        if 'FCNT' in full_name and 'MDu70' in full_name:
                            simple_name='MDuFCNT70'
                        if 'FCNT' in full_name and 'MXc03' in full_name:
                            simple_name='MXcFCNT03'
                        if 'FCNT' in full_name and 'EDu03' in full_name:
                            simple_name='EDuFCNT03'


                        if 'Docomo H01000 Decorative' in full_name:
                            simple_name='H01000 Decorative'

                        #如果‘M+数字’在full name中：
                        if re.match('M[0-9]+',fullname) is not None and re.match('M[0-9]+',fullname).group() in full_name:    
                            simple_name=re.match('M[0-9]+',fullname).group()                     
                        # 如果test_name 以 foldable_product_list 中的任何一个产品名开始，则返回true: any(test_name.startswith(product) for product in foldable_product_list)
                        if 'MXc15' in full_name and any(test_name.startswith(product) for product in foldable_product_list):
                            simple_name='MXc-US15' 
                        if 'MDu30' in full_name and any(test_name.startswith(product) for product in foldable_product_list):
                            simple_name='MDu-US30' 
                        if 'MDu72' in full_name and any(test_name.startswith(product) for product in foldable_product_list):
                            simple_name='MDu-US72' 

                        if simple_name=='MDu31' and In_houseFTODM=='In-house' and ip_infor>='7' and vintage!='EVT' and vintage!='DVT1':
                            simple_name='MDuAIR31' 
                        if simple_name=='MDu31' and In_houseFTODM!='In-house' and ip_infor>='7' and vintage!='EVT' and vintage!='DVT1':
                            simple_name='MDuWater31' 
                        if simple_name=='MDu45' and In_houseFTODM=='In-house' and ip_infor>='7' and vintage!='EVT' and vintage!='DVT1':
                            simple_name='MDuAIR45' 
                        if simple_name=='MDu45' and In_houseFTODM!='In-house' and ip_infor>='7' and vintage!='EVT' and vintage!='DVT1':
                            simple_name='MDuWater45' 

                        EDu03_name_list=['EDu03-0','EDu03-1','EDu03-2','EDu03-3','EDu03-4','EDu03-5','EDu03-6','EDu03-7'] 
                        EDu03_name_list_US=['EDu03-US-0','EDu03-US-1','EDu03-US-2','EDu03-US-3','EDu03-US-4','EDu03-US-5','EDu03-US-6','EDu03-US-7'] 
                        EDu03_name_list_FCNT=['EDuFCNT03-0','EDuFCNT03-1','EDuFCNT03-2','EDuFCNT03-3','EDuFCNT03-4','EDuFCNT03-5','EDuFCNT03-6','EDuFCNT03-7'] 
                        EDu03_name_list_US_FCNT=['EDuFCNT03-US-0','EDuFCNT03-US-1','EDuFCNT03-US-2','EDuFCNT03-US-3','EDuFCNT03-US-4','EDuFCNT03-US-5','EDuFCNT03-US-6','EDuFCNT03-US-7']

                        ATT30_name_list=['ATT30 ESD-0','ATT30 ESD-1','ATT30 ESD-2','ATT30 ESD-3','ATT30 ESD-4','ATT30 ESD-5','ATT30 ESD-6','ATT30 ESD-7'] 


                        #写入test plan表格的不同sheet
                
            
                        if 'ALT' in full_name: #写入ALT sheet
                            data_dict={'B':product_name,'C':test_no,'D':qty,'E':vintage,'F':category,'G':simple_name,'H':arr_date,'L':1,'N':priority, 'R':comments,'Q':lab_engineer,'AK':todaycolumn}
                            worksheet_alt.append(data_dict)
                            if product_name in foldable_product_list:
                                data_dict={'B':product_name,'C':test_no+'-1','D':4,'E':vintage,'F':category,'G':'Group C','H':arr_date,'L':1,'N':priority, 'R':comments,'Q':lab_engineer,'AK':todaycolumn}
                                worksheet_alt.append(data_dict)                               

                        elif 'DVT' in full_name or 'KDDI' in full_name or 'FCNT' in full_name or 'TA-' in full_name or 'Orange' in full_name or 'CEC' in full_name or 'ATT' in full_name or 'TMO' in full_name or 'Softbank' in full_name or 'Docomo' in full_name or 'LotX' in full_name or (re.match('M[0-9]+',fullname) is not None and re.match('M[0-9]+',fullname).group() in full_name): #写入DVT sheet
                            #在DVT sheet中分类别导入
                            if 'EDu03' in simple_name and ('FCNT' not in simple_name) and not any(test_name.startswith(product) for product in foldable_product_list):
                                for i in range(0,qty+1):
                                    simple_name=EDu03_name_list[i]
                                    test_no_i=test_no+'-'+str(i)
                                    data_dict={'B':product_name,'C':test_no_i,'D':qty,'E':vintage,'F':category,'G':simple_name,'H':arr_date,'L':1,'N':priority,'R':comments,'Q':lab_engineer,'AK':todaycolumn}
                                    worksheet_dvt.append(data_dict)  
                                i=7  
                                simple_name=EDu03_name_list[i]
                                test_no_i=test_no+'-'+str(i)
                                data_dict={'B':product_name,'C':test_no_i,'D':qty,'E':vintage,'F':category,'G':simple_name,'H':arr_date,'L':1,'N':priority,'R':comments,'Q':lab_engineer,'AK':todaycolumn}
                                worksheet_dvt.append(data_dict)                                 
                            elif 'EDu03' in simple_name and ('FCNT' not in simple_name) and any(test_name.startswith(product) for product in foldable_product_list):
                                for i in range(0,qty+1):
                                    simple_name=EDu03_name_list_US[i]
                                    test_no_i=test_no+'-'+str(i)
                                    data_dict={'B':product_name,'C':test_no_i,'D':qty,'E':vintage,'F':category,'G':simple_name,'H':arr_date,'L':1,'N':priority,'R':comments,'Q':lab_engineer,'AK':todaycolumn}
                                    worksheet_dvt.append(data_dict)  
                                i=7  
                                simple_name=EDu03_name_list_US[i]
                                test_no_i=test_no+'-'+str(i)
                                data_dict={'B':product_name,'C':test_no_i,'D':qty,'E':vintage,'F':category,'G':simple_name,'H':arr_date,'L':1,'N':priority,'R':comments,'Q':lab_engineer,'AK':todaycolumn}
                                worksheet_dvt.append(data_dict)    
                            elif 'EDuFCNT03' in simple_name and not any(test_name.startswith(product) for product in foldable_product_list):
                                for i in range(0,qty+1):
                                    simple_name=EDu03_name_list_FCNT[i]
                                    test_no_i=test_no+'-'+str(i)
                                    data_dict={'B':product_name,'C':test_no_i,'D':qty,'E':vintage,'F':category,'G':simple_name,'H':arr_date,'L':1,'N':priority,'R':comments,'Q':lab_engineer,'AK':todaycolumn}
                                    worksheet_dvt.append(data_dict)  
                                i=7  
                                simple_name=EDu03_name_list_FCNT[i]
                                test_no_i=test_no+'-'+str(i)
                                data_dict={'B':product_name,'C':test_no_i,'D':qty,'E':vintage,'F':category,'G':simple_name,'H':arr_date,'L':1,'N':priority,'R':comments,'Q':lab_engineer,'AK':todaycolumn}
                                worksheet_dvt.append(data_dict)                                 
                            elif 'EDuFCNT03' in simple_name and any(test_name.startswith(product) for product in foldable_product_list):
                                for i in range(0,qty+1):
                                    simple_name=EDu03_name_list_US_FCNT[i]
                                    test_no_i=test_no+'-'+str(i)
                                    data_dict={'B':product_name,'C':test_no_i,'D':qty,'E':vintage,'F':category,'G':simple_name,'H':arr_date,'L':1,'N':priority,'R':comments,'Q':lab_engineer,'AK':todaycolumn}
                                    worksheet_dvt.append(data_dict)  
                                i=7  
                                simple_name=EDu03_name_list_US_FCNT[i]
                                test_no_i=test_no+'-'+str(i)
                                data_dict={'B':product_name,'C':test_no_i,'D':qty,'E':vintage,'F':category,'G':simple_name,'H':arr_date,'L':1,'N':priority,'R':comments,'Q':lab_engineer,'AK':todaycolumn}
                                worksheet_dvt.append(data_dict)    

                            elif 'ATT30' in simple_name:
                                
                                for i in range(0,qty+1):

                                    simple_name=ATT30_name_list[i]
                                    test_no_i=test_no+'-'+str(i)
                                    data_dict={'B':product_name,'C':test_no_i,'D':qty,'E':vintage,'F':category,'G':simple_name,'H':arr_date,'L':1,'N':priority,'R':comments,'Q':lab_engineer,'AK':todaycolumn}
                                    worksheet_dvt.append(data_dict) 
                                i=7
                                simple_name=ATT30_name_list[i]
                                test_no_i=test_no+'-'+str(i)
                                data_dict={'B':product_name,'C':test_no_i,'D':qty,'E':vintage,'F':category,'G':simple_name,'H':arr_date,'L':1,'N':priority,'R':comments,'Q':lab_engineer,'AK':todaycolumn}
                                worksheet_dvt.append(data_dict) 

                            elif 'ATT11' in simple_name:
                                for i in range(0,4):
                                    simple_name='ATT11 Drop'+'-'+str(i)
                                    test_no_i=test_no+'-'+str(i)
                                    data_dict={'B':product_name,'C':test_no_i,'D':qty,'E':vintage,'F':category,'G':simple_name,'H':arr_date,'L':1,'N':priority,'R':comments,'Q':lab_engineer,'AK':todaycolumn}
                                    worksheet_dvt.append(data_dict) 
                            elif 'ATT12' in simple_name:
                                for i in range(1,4):
                                    simple_name='ATT12 Tumble'+'-'+str(i)
                                    test_no_i=test_no+'-'+str(i)
                                    data_dict={'B':product_name,'C':test_no_i,'D':qty,'E':vintage,'F':category,'G':simple_name,'H':arr_date,'L':1,'N':priority,'R':comments,'Q':lab_engineer,'AK':todaycolumn}
                                    worksheet_dvt.append(data_dict) 
                            elif 'ATT13' in simple_name:
                                for i in [1,3]:
                                    simple_name='ATT13 Vibration'+'-'+str(i)
                                    test_no_i=test_no+'-'+str(i)
                                    data_dict={'B':product_name,'C':test_no_i,'D':qty,'E':vintage,'F':category,'G':simple_name,'H':arr_date,'L':1,'N':priority,'R':comments,'Q':lab_engineer,'AK':todaycolumn}
                                    worksheet_dvt.append(data_dict) 
                            elif 'ATT14' in simple_name:
                                for i in [1,3]:
                                    simple_name='ATT14 Temp'+'-'+str(i)
                                    test_no_i=test_no+'-'+str(i)
                                    data_dict={'B':product_name,'C':test_no_i,'D':qty,'E':vintage,'F':category,'G':simple_name,'H':arr_date,'L':1,'N':priority,'R':comments,'Q':lab_engineer,'AK':todaycolumn}
                                    worksheet_dvt.append(data_dict) 
                            elif 'TMO6533' in simple_name:
                                for i in range(1,3):
                                    simple_name='TMO6533 Drop'+'-'+str(i)
                                    test_no_i=test_no+'-'+str(i)
                                    data_dict={'B':product_name,'C':test_no_i,'D':qty,'E':vintage,'F':category,'G':simple_name,'H':arr_date,'L':1,'N':priority,'R':comments,'Q':lab_engineer,'AK':todaycolumn}
                                    worksheet_dvt.append(data_dict) 

                            elif 'MDu20' in simple_name or 'MDu-LotX-20' in simple_name:
                                for i in range(0,2):
                                    if i==0:
                                        simple_name=simple_name
                                        test_no_i=test_no+'-'+str(i)
                                        data_dict={'B':product_name,'C':test_no_i,'D':qty,'E':vintage,'F':category,'G':simple_name,'H':arr_date,'L':1,'N':priority,'R':comments,'Q':lab_engineer,'AK':todaycolumn}
                                        worksheet_dvt.append(data_dict) 
                                    else:
                                        simple_name='MTS Measurement'
                                        test_no_i=test_no+'-'+str(i)
                                        data_dict={'B':product_name,'C':test_no_i,'D':qty,'E':vintage,'F':category,'G':simple_name,'H':arr_date,'L':1,'N':priority,'R':comments,'Q':lab_engineer,'AK':todaycolumn}
                                        worksheet_dvt.append(data_dict)                                         
                            elif 'MDu31' in simple_name or 'MDu-LotX-31' in simple_name or 'MDu-LotXAIR-31' in simple_name or 'MDuAIR31' in simple_name or 'MDuWater31' in simple_name or 'MDuFCNT31' in simple_name or 'MDuFCNTAIR31' in simple_name or 'MDuFCNTWater31' in simple_name:
                                for i in range(0,2):
                                    if i==0:
                                        simple_name=simple_name
                                        test_no_i=test_no+'-'+str(i)
                                        data_dict={'B':product_name,'C':test_no_i,'D':qty,'E':vintage,'F':category,'G':simple_name,'H':arr_date,'L':1,'N':priority,'R':comments,'Q':lab_engineer,'AK':todaycolumn}
                                        worksheet_dvt.append(data_dict) 
                                    else:
                                        simple_name='MTS Measurement'
                                        test_no_i=test_no+'-'+str(i)
                                        data_dict={'B':product_name,'C':test_no_i,'D':qty,'E':vintage,'F':category,'G':simple_name,'H':arr_date,'L':1,'N':priority,'R':comments,'Q':lab_engineer,'AK':todaycolumn}
                                        worksheet_dvt.append(data_dict)     



                            elif 'MDu-83' in simple_name:
                                for i in range(1,3):
                                    simple_name='MDu-83'+'-'+str(i)
                                    test_no_i=test_no+'-'+str(i)
                                    data_dict={'B':product_name,'C':test_no_i,'D':qty,'E':vintage,'F':category,'G':simple_name,'H':arr_date,'L':1,'N':priority,'R':comments,'Q':lab_engineer,'AK':todaycolumn}
                                    worksheet_dvt.append(data_dict) 


                            elif 'TMO6534' in simple_name:
                                for i in range(1,3):
                                    simple_name='TMO6534 Drop'+'-'+str(i)
                                    test_no_i=test_no+'-'+str(i)
                                    data_dict={'B':product_name,'C':test_no_i,'D':qty,'E':vintage,'F':category,'G':simple_name,'H':arr_date,'L':1,'N':priority,'R':comments,'Q':lab_engineer,'AK':todaycolumn}
                                    worksheet_dvt.append(data_dict) 


                            elif 'ATT28' in simple_name:
                                simple_name='ATT28 Life'+'-'+'0'
                                test_no_i=test_no+'-'+'0'
                                data_dict={'B':product_name,'C':test_no_i,'D':qty,'E':vintage,'F':category,'G':simple_name,'H':arr_date,'L':1,'N':priority,'R':comments,'Q':lab_engineer,'AK':todaycolumn}
                                worksheet_dvt.append(data_dict) 

                            else:
                                # print(test_no,'else')
                                data_dict={'B':product_name,'C':test_no,'D':qty,'E':vintage,'F':category,'G':simple_name,'H':arr_date, 'L':1,'N':priority,'R':comments,'Q':lab_engineer,'AK':todaycolumn}
                                # print(data_dict)
                                worksheet_dvt.append(data_dict)                       

                            

                        else:  #写入accessory sheet
                            if 'Torque Study' in simple_name:                                       
                                for i in range(1,3):
                                    simple_name='Torque Study'
                                    test_no_i=test_no+'-'+str(i)
                                    data_dict={'B':product_name,'C':test_no_i,'D':qty,'E':vintage,'F':category,'G':simple_name,'H':arr_date,'L':1,'N':priority,'R':comments,'Q':lab_engineer,'AK':todaycolumn}
                                    worksheet_accessory.append(data_dict) 
                            else:
                                data_dict={'B':product_name,'C':test_no,'D':qty,'E':vintage,'F':category,'G':simple_name,'H':arr_date, 'L':1,'N':priority,'R':comments,'Q':lab_engineer,'AK':todaycolumn}
                                worksheet_accessory.append(data_dict)    
                        print('已导入',m,':',product_name, test_no,'Arrive:',str(arr_date)[0:10],simple_name)
                    else:
                        print('####未导入:',product_name, test_no,'Arrive:',str(arr_date)[0:10])   #因不满足条件未去查询ALTDB

                # workbook1.save(test_plan_file)
                # workbook1 = vb.load_workbook(test_plan_file)  #20250405新增2行
                
                
                #test plan表格的格式处理
                for sheet_name in ['ALT','DVT','Accessory']:
                    for row in range(2,workbook1[sheet_name].max_row+1):
                        for col in range(4,5):
                            workbook1[sheet_name].cell(row,col).number_format = '0'    #Qty
                        for col in range(8,9):
                            workbook1[sheet_name].cell(row,col).number_format = 'mm/dd'  #arrive date
                        for col in range(9,10):
                            workbook1[sheet_name].cell(row,col).number_format = 'mm/dd'  #start date
                        for col in range(10,11):
                            workbook1[sheet_name].cell(row,col).number_format = 'mm/dd'  #comp date
                        for col in range(11,15):
                            workbook1[sheet_name].cell(row,col).number_format = '@'
                        for col in range(1,19):
                            workbook1[sheet_name].cell(row,col).border=border
                            workbook1[sheet_name].cell(row,col).font=fontsetting

                        for col in range(1,18): # comments列不做居中处理
                            workbook1[sheet_name].cell(row,col).alignment = alignment   
                
                        if workbook1[sheet_name].cell(row,14).value == 3:   #priority=1时
                            workbook1[sheet_name].cell(row,7).fill = pattern_fill    #test_name fill黄色
                    
                workbook1.save(test_plan_file)
                #把每个产品的导入数量写入字典

                dict[str(lab_engineer+'-'+product_name+'导入 ')]=m
            



            
    #.建立mysql的连接
    conn = pymysql.connect(host=ip_address,user='labuser',db='exceltodb',
            password='123456',port=3306,charset='utf8')
    # 获得游标
    cursor = conn.cursor()
    #记录使用情况：导入人，被导入人，产品，数量，时间     
    query='''insert into test_im_record(user_name,productname,labengineer,numbers,import_time) values(%s,%s,%s,%s,%s)'''
    user_name = getpass.getuser()  
    for key in dict:
        productname = re.findall(r'-(.*?)导入', key)[0] #匹配of 和空格之间的产品名
        labengineer = re.findall(r'([\u4e00-\u9fa5]+)', key)[0] #匹配of 和空格之间的产品名
        numbers = dict[key]
        import_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') 

        values =(user_name,productname,labengineer,numbers,import_time)
        # 执行sql语句
        cursor.execute(query,values) 
        conn.commit()  

    print('完成导入：',dict)
    print('进行产品readiness检查......')
    return(dict)

def readiness_check( ):    
    pre_days_upload=(datetime.datetime.now()+datetime.timedelta(days=0)).strftime('%Y-%m-%d')
    # 格式化pre_days_upload, 为了比较大小
    Xdaysago = datetime.datetime.strptime(pre_days_upload,"%Y-%m-%d") 
    fontsetting1 = Font(name=u'Arial', bold=False, italic=False, size=10,color='FF0000')

    #.打开Excel,读取worksheet
    server_file=r'\\10.114.184.9\Tcc_Rel\04 Task_Assignment\产品实验准备checklist\New prodct readiness checklist.xlsx'
    test_plan_file=r'C:\自动分活儿\Reliability Test plan.xlsx'
    workbook1 = vb.load_workbook(test_plan_file)  
    workbook2 = vb.load_workbook(server_file)  
    test_requirements_sheet=workbook2['Test_requirements']
    product_readiness_sheet=workbook2['Product_readiness']

    sheet_names=['ALT','DVT','Accessory','Other']
    
    for i in sheet_names:
        plan_worksheet=workbook1[i]
        n=plan_worksheet.max_row-5  #防止有时sheet行数太少的情况
        if n>=200:
            n=200
        for rownumber in range(plan_worksheet.max_row,plan_worksheet.max_row-n,-1):      #range,从最大行开始往回遍历，删除行都要从后往前才行
            for cell in plan_worksheet[rownumber][36:37]:     #以[]最后一位为准       
                if cell.value is not None and cell.value == Xdaysago.strftime('%Y-%m-%d'):  
                    for cell in plan_worksheet[rownumber][1:2]:
                        product_name=cell.value
                        # print(product_name)
                    for cell in plan_worksheet[rownumber][6:7]:
                        test_name=cell.value
                        # print(product_name,test_name,'满足日期要求的行')
                    # plan_worksheet.cell(rownumber,23,value='write-here!')           

                    for cell in test_requirements_sheet['A']:   #遍历第一列
                        if cell.value==test_name:   #找到test_name
                            # print(cell.value,cell.row)  # test_name, 行号
                            this_row=test_requirements_sheet[cell.row]  #找到这一行，下面对这行进行遍历
                            requirement_cols=[]
                            for col in this_row:
                                if col.value=="Y":      #如果行中的单元格=Y，则把列号加入列表中
                                    requirement_cols.append(col.column)
                            # print(requirement_cols)

                            #用和上面相同方法遍历product_readiness表格的第一列
                            productlist=[] #readiness_sheet中已包含的产品列表
                            for cell in product_readiness_sheet['A']:   #遍历第一列,先看看该产品是否已经在readiness中更新过了
                                productlist.append(cell.value)
                            if product_name in productlist:   
                                for cell in product_readiness_sheet['A']:   #遍历第一列
                                    if cell.value==product_name:   #找到product_name
                                        # print(cell.value,cell.row)  # product_name, 行号
                                        this_row=product_readiness_sheet[cell.row]  #找到这一行，下面对这行进行遍历
                                        unreadiness_cols=[]
                                        for col in requirement_cols:
                                            if product_readiness_sheet.cell(cell.row,col).value=="Not ready":      #如果行中的单元格="Not ready"，则把列标题内容加入列表中
                                                unreadiness_cols.append(product_readiness_sheet.cell(1,col).value)
                                        if unreadiness_cols!=[]:
                                            print(product_name,test_name,'缺少：',unreadiness_cols)
                                            comments=''
                                            for i in range(0,len(unreadiness_cols)):
                                                comments=comments+unreadiness_cols[i]+','

                                            comments='缺少:'+comments
                                            plan_worksheet.cell(rownumber,23,value=comments)  
                            else:
                                plan_worksheet.cell(rownumber,23,value='产品checklist待更新!')   

                            plan_worksheet.cell(rownumber,23).font=fontsetting1         

    print('产品readiness检查完成！')
    workbook1.save(test_plan_file)



def coefficient_update(ip_address):
    
    #1.获取当前日期  
    todaycolumn=datetime.datetime.now().strftime('%Y-%m-%d') 
    cellbackground0 = vb.styles.PatternFill(fill_type='gray125') 

    #2.打开Excel,读取worksheet
    filepath = r'C:\自动分活儿\Reliability Test plan.xlsx'
    workbook = vb.load_workbook(filepath) 

    #3.建立mysql的连接
    conn = pymysql.connect(host=ip_address,user='labuser',db='exceltodb',
            password='123456',port=3306,charset='utf8')
    # 获得游标
    cursor = conn.cursor()

    worksheet1 = workbook['ALT']
    # ALT表格处理最后20行，DVT表格处理最后60行
    maxrows=worksheet1.max_row + 1  #此处必须+1，否则在range中不会处理最后一个
    if maxrows>=21:
        initialrow=maxrows-19  
    else:
        initialrow=2  
    n=0 
    for i in range(2,maxrows):   
        product_name= worksheet1.cell(i,2).value 
        test_name= worksheet1.cell(i,7).value 
        import_date= worksheet1.cell(i,37).value
        if import_date==todaycolumn and test_name.startswith('ALT'):
            print(product_name,test_name)   
            cursor.execute('''select product,ALT_Score from alt_infor where product like %s limit 1 ''',(product_name+'%'))
            coe_return1 = cursor.fetchone()
            if coe_return1:
                print(coe_return1[0],coe_return1[1])
                worksheet1.cell(i,12).value = float(coe_return1[1])
                worksheet1.cell(i,12).fill = cellbackground0 
                n=n+1
    # print('ALT Sheet:',n,'个实验系数已更新！')     # 只有test_name以ALT开头且当日导入的实验才会更新系数，其它不更新   
    tip1='ALT Sheet:'+str(n)+'个实验系数已更新.'
    print(tip1)        

    worksheet2 = workbook['DVT']
    maxrows=worksheet2.max_row + 1  #此处必须+1，否则在range中不会处理最后一个
    if maxrows>=61:
        initialrow=maxrows-59  
    else:
        initialrow=2 
    n=0 
    for i in range(initialrow,maxrows):   
        product_name= worksheet2.cell(i,2).value
        test_category= worksheet2.cell(i,6).value 
        test_name= worksheet2.cell(i,7).value
        # arrive_date= worksheet.cell(i,8).value
        # old_coefficient= worksheet2.cell(i,12).value
        import_date= worksheet2.cell(i,37).value
        if import_date==todaycolumn:
            # print(product_name,test_category,test_name,type(old_coefficient))   
            cursor.execute('''select product,test_category,test_name,coefficient from coefficient_infor 
            where product=%s and test_category=%s and test_name=%s''',(product_name,test_category,test_name))
            coe_return2 = cursor.fetchone()
            if coe_return2: 
                print('折叠机DVT系数处理:',product_name,test_category,test_name,coe_return2[3])      
                worksheet2.cell(i,12).value = float(coe_return2[3])
                worksheet2.cell(i,12).fill = cellbackground0 
                n=n+1
            else:
                cursor.execute('''select product,test_category,test_name,coefficient from coefficient_infor 
                where product='Candybar' and test_category=%s and test_name=%s''',(test_category,test_name))
                coe_return3 = cursor.fetchone()    
                if coe_return3: 
                    print('直板机DVT系数处理:',product_name,test_category,test_name,coe_return3[3])     
                    
                    worksheet2.cell(i,12).value = float(coe_return3[3])
                    worksheet2.cell(i,12).fill = cellbackground0 
                    n=n+1

    tip2='DVT Sheet:'+str(n)+'个实验系数已更新.'
    print(tip2)          
    workbook.save(filepath)  
    return(tip1,tip2)


if __name__=="__main__":    
    # ip_address='localhost'
    ip_address='10.114.183.55'
    # lab_engineers=['汪焕']
    lab_engineers=['陈凯','赵俊凯','张永庆','汪焕']
    read_write(lab_engineers,ip_address)
    # readiness_check( )
    # coefficient_update(ip_address)



#加入新的test_name的步骤：
# 1. 命名好全名的 test_name，放入ALT DB，把简化名写入 test plan 表格中的 Data 中的 test_name列
# 2. 更新自动分活表格，使用简化test_name，并导入数据库。
# 3. 运行一次分活儿之后，Data 中的简化test_name会被导入数据库，然后在导入时，根据实验号查询ALTDB,获得全名的testname, 根据全名再匹配简化名，再导入包含新test_name的实验到test plan表格中

# #导入时，根据实验号在ALT DB 查询：通过test_no查出 Qty, vintage, 全名test_name, test_name 要变为简写，再通过全名test_name要给出category，写入到test plan表格中
# 导入时的产品名并不是用ALTDB根据实验号查询到的，而是根据导入的excel文件名写入test plan 的, TQM每个人确保自己的文件名中有产品名

